import time
import openpyxl
from openpyxl.utils import column_index_from_string
import requests
from typing import Optional, Tuple
from urllib.parse import urlparse
import logging

# 配置简洁的日志输出
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s",
    handlers=[logging.StreamHandler()],
)
logger = logging.getLogger(__name__)


def get_OSChina_article_views(url: str) -> Optional[int]:
    """
    从OSChina文章URL获取阅读数
    """
    try:
        # 从URL中提取文章ID
        parsed_url = urlparse(url)
        path_parts = parsed_url.path.split("/")

        # 尝试从路径中获取文章ID
        article_id = None
        for part in reversed(path_parts):
            if part.isdigit():
                article_id = part
                break

        if not article_id:
            return None

        # 构建API请求URL
        api_url = f"https://apiv1.oschina.net/oschinapi/blog/detail?id={article_id}"

        # 设置请求头
        headers = {
            "accept": "application/json, text/plain, */*",
            "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
        }

        logger.info(f"正在获取URL的阅读数: {url}")

        # 发送请求
        response = requests.get(api_url, headers=headers, timeout=10)
        response.raise_for_status()

        # 解析响应
        data = response.json()
        if data.get("success") and "result" in data:
            views = data["result"].get("realCount")
            logger.info(f"成功获取阅读数: {views}")
            return views
        return None

    except Exception:
        return None


def validate_url(url: str) -> bool:
    """验证URL是否为有效的OSChina文章URL"""
    return (
        isinstance(url, str)
        and url.strip().startswith("http")
        and "oschina.net" in url
        and "/blog/" in url
    )


def process_excel_file(
    file_path: str,
    sheet_name: Optional[str] = None,
    url_column: str = "N",
    views_column: str = "O",
    delay: float = 1.0,
) -> Tuple[int, int]:
    """
    处理Excel文件，获取OSChina文章阅读数并更新到指定列
    """
    try:
        logger.info("开始处理Excel文件")

        # 加载Excel文件
        wb = openpyxl.load_workbook(file_path, data_only=True)
        ws = wb[sheet_name] if sheet_name else wb.active

        # 转换列索引
        url_col_idx = column_index_from_string(url_column)
        views_col_idx = column_index_from_string(views_column)

        success_count = 0
        total_processed = 0

        # 遍历URL列（从第2行开始，跳过标题行）
        for row_idx, row in enumerate(
            ws.iter_rows(
                min_row=2, min_col=url_col_idx, max_col=url_col_idx, values_only=True
            ),
            start=2,
        ):
            url_cell = row[0]

            if not url_cell or not validate_url(url_cell):
                continue

            total_processed += 1

            # 获取阅读数
            views = get_OSChina_article_views(url_cell)

            if views is not None:
                # 更新阅读数列
                ws.cell(row=row_idx, column=views_col_idx, value=views)
                success_count += 1
                logger.info(f"行 {row_idx}: URL={url_cell}, Views={views}")
            else:
                ws.cell(row=row_idx, column=views_col_idx, value="None")
                logger.warning("行 %s: 获取失败", row_idx)

            # 延迟以避免频繁请求
            time.sleep(delay)

        # 保存文件
        wb.save(file_path)

        return success_count, total_processed

    except Exception as e:
        logger.error("处理失败: %s", e)
        raise


def main():
    """主函数"""
    try:
        success_count, total_count = process_excel_file(
            file_path="C:/Users/qwx1425249/Desktop/data/开发者空间案例内外媒运营数据看板.xlsx",
            sheet_name="案例发布计划",
            url_column="N",
            views_column="O",
            delay=1.0,
        )

        logger.info(
            "获取结果: 成功 %s, 失败 %s", success_count, total_count - success_count
        )

    except KeyboardInterrupt:
        logger.info("用户中断")
    except Exception as e:
        logger.error("程序错误: %s", e)


if __name__ == "__main__":
    main()
